package com.glsc.ngateway.common.base.repo.mysql.gateway.platform.it;


import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.it.ItArpDevice;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;


@Repository
public interface ItArpDeviceRepo extends JpaRepository<ItArpDevice, Integer> {

    @Transactional
    @Modifying
    @Query(value = "truncate table t_it_arp_device", nativeQuery = true)
    Integer truncateTableItArpDevice();


    List<ItArpDevice> findBySwitchIpAndSwitchInterfaceAndMacAddress(String switchIp, String switchInterface, String macAddress);
//    String sql = "select * from t_arp_device a join " +
//            "(select mac1,mac2,mac3,mac4,mac5,mac6,'' as virtual_name,'' as virtual_ip," +
//            "business_name,application_detail,machine_room,machine_cab,machine_unit,ip1,ip2,user_id from t_service_device " +
//            "union all " +
//            "select c.mac1,c.mac2,'','','','',c.virtual_name,d.ip1,c.business_name, " +
//            "c.application_detail,'','','',c.ip1,c.ip2,c.user_id from t_virtual_device c left join t_service_device d " +
//            "on c.virtual_ip = d.dev_id) b on (a.mac_address = b.mac1) or (a.mac_address = b.mac2) or (a.mac_address = b.mac3) " +
//            "or (a.mac_address = b.mac4) or (a.mac_address = b.mac5)  or (a.mac_address = b.mac6) ";


    //        @Query(value = "select a from ItArpDevice a join " +
//            "( select m.mac1, m.mac2, m.mac3, m.mac4, m.mac5, m.mac6, '' as virtualName, '' as virtualIp, " +
//            "m.businessName, m.applicationDetail, m.machineRoom, m.machineCab, m.machineUnit, m.ip1, m.ip2, m.userId from ItServerDevice m " +
//            "union all " +
//            "select c.mac1, c.mac2, '' as mac3 , '' as mac4 , '' as mac5 , '' as mac6 , c.virtualName, d.ip1 , c.businessName, " +
//            "c.applicationDetail,'' as machineRoom ,'' as machineCab ,'' as machineUnit ,c.ip1,c.ip2,c.userId from ItVirtualDevice c left join ItServiceDevice d " +
//            "on c.virtualIp = d.devId ) b on (a.macAddress = b.mac1) or (a.macAddress = b.mac2) or (a.macAddress = b.mac3) " +
//            "or (a.macAddress = b.mac4) or (a.macAddress = b.mac5)  or (a.macAddress = b.mac6) ")
//    @Query(value = "select a.switch_ip as switchIp, a.switch_interface as switchInterface, a.mac_address as macAddress, " +
//            "b.mac1,b.mac2,b.mac3,b.mac4,b.mac5,b.mac6,b.virtualName,b.virtualIp,b.businessName,b.applicationDetail,b.machineRoom,b.machineCab,b.machineUnit,b.ip1,b.ip2,b.userId " +
//            "from t_it_arp_device a join " +
//            "(select mac1,mac2,mac3,mac4,mac5,mac6,'' as virtualName,'' as virtualIp," +
//            "business_name as businessName,application_detail as applicationDetail,machine_room as machineRoom, " +
//            "machine_cab as machineCab,machine_unit as machineUnit ,ip1,ip2,user_id as userId from t_it_server_device " +
//            "union all " +
//            "select c.mac1,c.mac2,'' as mac3,'' as mac4,'' as mac5,'' as mac6,c.virtual_name as virtualName,d.ip1 as virtualIp,c.business_name as businessName, " +
//            "c.application_detail as applicationDetail,'' as machineRoom,'' as machineCab ,'' as machineUnit ,c.ip1,c.ip2,c.user_id as userId from t_it_virtual_device c left join t_it_server_device d " +
//            "on c.virtual_ip = d.dev_id) b on (a.mac_address = b.mac1) or (a.mac_address = b.mac2) or (a.mac_address = b.mac3) " +
//            "or (a.mac_address = b.mac4) or (a.mac_address = b.mac5)  or (a.mac_address = b.mac6) "
//            , countQuery = "select count(*) from t_it_arp_device a join " +
//            "(select mac1,mac2,mac3,mac4,mac5,mac6,'' as virtualName,'' as virtualIp," +
//            "business_name as businessName,application_detail as applicationDetail,machine_room as machineRoom, " +
//            "machine_cab as machineCab,machine_unit as machineUnit ,ip1,ip2,user_id as userId from t_it_server_device " +
//            "union all " +
//            "select c.mac1,c.mac2,'' as mac3,'' as mac4,'' as mac5,'' as mac6,c.virtual_name as virtualName,d.ip1 as virtualIp,c.business_name as businessName, " +
//            "c.application_detail as applicationDetail,'' as machineRoom,'' as machineCab ,'' as machineUnit ,c.ip1,c.ip2,c.user_id as userId from t_it_virtual_device c left join t_it_server_device d " +
//            "on c.virtual_ip = d.dev_id) b on (a.mac_address = b.mac1) or (a.mac_address = b.mac2) or (a.mac_address = b.mac3) " +
//            "or (a.mac_address = b.mac4) or (a.mac_address = b.mac5)  or (a.mac_address = b.mac6) ",
//            nativeQuery = true)
//    Page<List<Map<String, Object>>> getSwitchBoardList(Pageable pageable);


    @Query(value = "select ROW_NUMBER() over (order by a.id) as id , a.id as arpid, " +
            "b.userName , a.switch_ip as switchIp, a.switch_interface as switchInterface, a.mac_address as macAddress, " +
            "b.mac1,b.mac2,b.mac3,b.mac4,b.mac5,b.mac6,b.virtualName,b.virtualIp,b.businessName,b.applicationDetail,b.machineRoom,b.machineCab,b.machineUnit,b.ip1,b.ip2,b.userId " +
            "from t_it_arp_device a join " +
            "(select tt.name as userName, mac1,mac2,mac3,mac4,mac5,mac6,'' as virtualName,'' as virtualIp," +
            "business_name as businessName,application_detail as applicationDetail,machine_room as machineRoom, " +
            "machine_cab as machineCab,machine_unit as machineUnit ,ip1,ip2,user_id as userId from t_it_server_device dd " +
            "left join t_user tt on dd.user_id = tt.userid " +
            "union all " +
            "select ttt.name as userName, c.mac1,c.mac2,'' as mac3,'' as mac4,'' as mac5,'' as mac6,c.virtual_name as virtualName,d.ip1 as virtualIp,c.business_name as businessName, " +
            "c.application_detail as applicationDetail,'' as machineRoom,'' as machineCab ,'' as machineUnit ,c.ip1,c.ip2,c.user_id as userId from t_it_virtual_device c " +
            "left join t_it_server_device d on c.virtual_ip = d.dev_id " +
            "left join t_user ttt on c.user_id = ttt.userid " +
            ") b on (a.mac_address = b.mac1) or (a.mac_address = b.mac2) or (a.mac_address = b.mac3) " +
            "or (a.mac_address = b.mac4) or (a.mac_address = b.mac5)  or (a.mac_address = b.mac6) where 1 = 1 " +
            "and (:switchIp is null OR a.switch_ip = :switchIp )  " +
            "and (:switchInterface is null OR a.switch_interface = :switchInterface )  " +
            "and (:macAddress is null OR a.mac_address = :macAddress )  " +
            "and (:machineRoom is null OR b.machineRoom = :machineRoom )  " +
            "and (:machineCab is null OR b.machineCab = :machineCab)  " +
            "and (:virtualName is null OR b.virtualName = :virtualName)  " +
            "and (:ip1 is null OR b.ip1 = :ip1)  " +
            "and (:ip2 is null OR b.ip2 = :ip2)  " +
            "and (:virtualIp is null OR b.virtualIp = :virtualIp )  " +
            "and (:userId is null OR b.userId = :userId)  " +
            "and (:businessName is null OR b.businessName = :businessName)  " +
            "and (:applicationDetail is null OR b.applicationDetail = :applicationDetail)  "
            , countQuery = "select count(*) from t_it_arp_device a join " +
            "(select tt.name as userName, mac1,mac2,mac3,mac4,mac5,mac6,'' as virtualName,'' as virtualIp," +
            "business_name as businessName,application_detail as applicationDetail,machine_room as machineRoom, " +
            "machine_cab as machineCab,machine_unit as machineUnit ,ip1,ip2,user_id as userId from t_it_server_device dd " +
            "left join t_user tt on dd.user_id = tt.userid " +
            "union all " +
            "select ttt.name as userName, c.mac1,c.mac2,'' as mac3,'' as mac4,'' as mac5,'' as mac6,c.virtual_name as virtualName,d.ip1 as virtualIp,c.business_name as businessName, " +
            "c.application_detail as applicationDetail,'' as machineRoom,'' as machineCab ,'' as machineUnit ,c.ip1,c.ip2,c.user_id as userId from t_it_virtual_device c " +
            "left join t_it_server_device d on c.virtual_ip = d.dev_id " +
            "left join t_user ttt on c.user_id = ttt.userid " +
            ") b on (a.mac_address = b.mac1) or (a.mac_address = b.mac2) or (a.mac_address = b.mac3) " +
            "or (a.mac_address = b.mac4) or (a.mac_address = b.mac5)  or (a.mac_address = b.mac6) where 1 = 1 " +
            "and (:switchIp is null OR a.switch_ip = :switchIp )  " +
            "and (:switchInterface is null OR a.switch_interface = :switchInterface )  " +
            "and (:macAddress is null OR a.mac_address = :macAddress )  " +
            "and (:machineRoom is null OR b.machineRoom = :machineRoom )  " +
            "and (:machineCab is null OR b.machineCab = :machineCab)  " +
            "and (:virtualName is null OR b.virtualName = :virtualName)  " +
            "and (:ip1 is null OR b.ip1 = :ip1)  " +
            "and (:ip2 is null OR b.ip2 = :ip2)  " +
            "and (:virtualIp is null OR b.virtualIp = :virtualIp )  " +
            "and (:userId is null OR b.userId = :userId)  " +
            "and (:businessName is null OR b.businessName = :businessName)  " +
            "and (:applicationDetail is null OR b.applicationDetail = :applicationDetail) ",
            nativeQuery = true)
    Page<Map<String, Object>> searchSwitchBoard(Pageable pageable, String businessName, String applicationDetail,
                                                String virtualName, String ip1, String ip2, Integer machineRoom,
                                                String machineCab, String switchIp, String switchInterface,
                                                String macAddress, Integer virtualIp, String userId);


}
